<?php
namespace Org\Net;
/**
 * Created by PhpStorm.
 * User: Administrator
 * Date: 2017/5/4
 * Time: 12:58
 */

class ExcelHelper
{

    /**
     * excel导入导出辅助类
     */

    const CONTENT_TYPE = 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet';//application/vnd.ms-excel
    private $fields = array();
    private $datas = array();

    function __construct($fields = array())
    {
        if (!empty($fields)) {
            $this->setFields($fields);
        }
    }

    /**
     * 设置字段
     * @param array $fields ['money' => '金额'] 或者 ['money' => ['text'=>'金额', 'type'=>'money', 'width'=>10]]
     */
    public function setFields($fields)
    {
        foreach ($fields as &$vo) {
            if (is_array($vo) && !isset($vo['type'])) {
                $vo['type'] = '';
            }
        }
        $this->fields = $fields;
    }

    /**
     * 设置数据集
     * @param array $datas 数据
     */
    public function setDatas($datas)
    {
        foreach ($datas as $row) {
            $this->addRow($row);
        }
    }

    /**
     * 添加一行数据
     * @param array $row 一行数据
     */
    public function addRow($row)
    {
        $row = $this->checkRow($row);
        if ($row !== false) {
            $this->datas[] = $row;
        }
    }

    /**
     * 导出excel
     * @param  string $fileName 下载文件名
     */
    public function export($fileName)
    {
        vendor('PHPExcel/PHPExcel');
        vendor('PHPExcel/IOFactory.php');
        vendor('PHPExcel/Cell/DataType.php');
        $objPHPExcel = new \PHPExcel();
        $objPHPExcel->getProperties()->setTitle('export')->setDescription('none');
        $objPHPExcel->setActiveSheetIndex(0);
        //表头生成
        $col = 0;
        foreach ($this->fields as $field) {
            $objActSheet = $objPHPExcel->getActiveSheet();
            if (is_array($field)) {
                $objActSheet->getColumnDimension(chr(65 + $col))->setWidth($field['width']);
                $objActSheet->setCellValueByColumnAndRow($col, 1, $field['text']);
            } else {
                $objActSheet->getColumnDimension(chr(65 + $col))->setAutoSize(true);
                $objActSheet->setCellValueByColumnAndRow($col, 1, $field);
            }
            $col++;
        }
        // Fetching the table data
        $keyFields = array_keys($this->fields);
        $row = 2;
        $sys_num = 1;
        foreach ($this->datas as $data) {
            $col = 0;
            foreach ($keyFields as $field) {
                if (is_array($this->fields[$field])) {
                    switch ($this->fields[$field]['type']) {
                        case 'sys_num':
                            //序号
                            $objPHPExcel->getActiveSheet()->setCellValueExplicitByColumnAndRow($col, $row, $sys_num, \PHPExcel_Cell_DataType::TYPE_NUMERIC);
                            $sys_num++;
                            break;
                        case 'select':
                            //选项类型
                            $objPHPExcel->getActiveSheet()->setCellValueExplicitByColumnAndRow($col, $row, $this->fields[$field]['options'][$data[$field]]);
                            $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col, $row)->getNumberFormat()->setFormatCode('@');
                            break;
                        case 'money':
                            //金额
                            $objPHPExcel->getActiveSheet()->setCellValueExplicitByColumnAndRow($col, $row, $data[$field], \PHPExcel_Cell_DataType::TYPE_NUMERIC);
                            break;
                        case 'content':
                            //静态内容
                            $objPHPExcel->getActiveSheet()->setCellValueExplicitByColumnAndRow($col, $row, $this->fields[$field]['value']);
                            $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col, $row)->getNumberFormat()->setFormatCode('@');
                            break;
                        default:
                            $objPHPExcel->getActiveSheet()->setCellValueExplicitByColumnAndRow($col, $row, $data[$field]);
                            $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col, $row)->getNumberFormat()->setFormatCode('@');
                    }
                } else {
                    $objPHPExcel->getActiveSheet()->setCellValueExplicitByColumnAndRow($col, $row, $data[$field]);
                    $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col, $row)->getNumberFormat()->setFormatCode('@');
                }
                $col++;
            }
            $row++;
        }
        $objPHPExcel->setActiveSheetIndex(0);
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
        ob_clean();
        ob_end_clean(); //清除缓冲区,避免乱码
        header('Content-Type: ' . self::CONTENT_TYPE);
        header('Content-Disposition: attachment;filename="' . $fileName . '.xlsx');
        header('Cache-Control: max-age=0');
        header("Pragma: no-cache");
        $objWriter->save('php://output');
    }

    /**
     * 检查一行数据是否与设定的字段匹配
     * @param  array $row 一行数据
     * @return boolean|array
     */
    private function checkRow($row)
    {
        if (!is_array($row) && !($row instanceof Model)) {
            return false;
        }
        $keys = array_keys($this->fields);
        $data = array();
        foreach ($keys as $key) {
            if (isset($row[$key])) {
                $data[$key] = $row[$key];
            } else {
                $data[$key] = '';
            }
        }
        return $data;
    }

}

/**
 * Created by PhpStorm.
 * User: Administrator
 * Date: 2017/5/4
 * Time: 10:16
 */

namespace Admin\Controller;

use Org\Net\ExcelHelper;

class ExcelController
{

    /**
     * @param array $fields 显示的标题
     * @param array $data 数据 二维数组
     * @param string $fileName 文件名
     * @description 导出数据到excel表格
     */
    public function exportData(array $fields = array(), array $data = array(), $fileName = '')
    {
        if (IS_GET) {

            $fields = empty($fields) ? E('字段不能为空') : $fields;
            $data = empty($data) ? E('数据不能为空') : $data;
            $fileName = isset($fileName) ? $fileName : E('文件名不能为空');

            $excel = new ExcelHelper($fields);

            foreach ($data as $vo) {
                $excel->addRow($vo);
            }
            $excel->export($fileName . date('Y-m-d'));
//        return Response::create()->contentType($excel::CONTENT_TYPE);
        }

    }

//            $excel = new ExcelHelper(array(
//            'title' => array('text' => '借款标名称', 'width' => 30),
//            'contract' => array('text' => '合同号', 'width' => 30),
//            'amount' => array('text' => '借款金额', 'width' => 50, 'type' => 'money'),
//            'deadline' => array('text' => '借款期限', 'width' => 10),
//                'amount' => array('text' => '提现金额', 'width' => 10, 'type' => 'money'),
//                'bank_general_name' => array('text' => '银行名称', 'width' => 20),
//                'bank_name' => array('text' => '开户行名称', 'width' => 30),
//                'account_no' => array('text' => '账号', 'width' => 25),
//                'account_name' => array('text' => '姓名', 'width' => 10),
//                'account_prop' => array('text' => '账户性质', 'width' => 10),
//                'create_time' => array('text' => '申请时间', 'width' => 20)
//            )
//
//           $arr = array(array('title' => '借款标名称','contract' => '合同号','amount' => '1000000','deadline' => '借款期限')
//);
//            );
//
//            $a=D('ProjectUserView');
//            $data=$a->select();
//            //        dump($data);
//            foreach ($data as $vo) {
//
//            $excel->addRow($vo);
//            }
//            $excel->export('结算单' . date('Y-m-d'));
//            //        return Response::create()->contentType(ExcelHelper::CONTENT_TYPE);
}
